package com.sigmazero13.gameutils.db;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.format.DateFormat;
import android.util.Log;
import android.widget.Toast;

import com.sigmazero13.gameutils.R;
import com.sigmazero13.gameutils.model.GameInfo;
import com.sigmazero13.gameutils.model.GameInfoCustom;
import com.sigmazero13.gameutils.model.GamePlayInfo;
import com.sigmazero13.gameutils.model.GamePlayerInfo;
import com.sigmazero13.gameutils.model.SyncInfo;

public class UtilDbHelper {

	private static final int DATABASE_VERSION = 4;
	
	private static final String GAME_TABLE = "games";
	private static final String GAME_ID = "id";
	private static final String GAME_NAME = "name";
	private static final String GAME_MINPLAYERS = "min_players";
	private static final String GAME_MAXPLAYERS = "max_players";
	private static final String GAME_BGGLENGTH = "bgg_length";
	private static final String GAME_BGGRATING = "bgg_rating";
	private static final String GAME_MANUAL = "bgg_manual";
	
	private static final String PLAY_TABLE = "play_info";
	private static final String PLAY_ID = "play_id";
	private static final String PLAY_GAME_ID = "game_id";
	private static final String PLAY_DATE = "played_on";
	private static final String PLAY_LOCATION = "location";  // V4
	private static final String PLAY_SCENARIO = "scenario";  // V4
	private static final String PLAY_LENGTH = "game_length";
	
	private static final String PLAYER_TABLE = "play_player";
	private static final String PLAYER_ID = "player_id";
	private static final String PLAYER_PLAY_ID = "play_id";
	private static final String PLAYER_NAME = "player_name";
	private static final String PLAYER_USERNAME = "username";
	private static final String PLAYER_TEAM = "team";
	private static final String PLAYER_STARTINGPOS = "starting_position";
	private static final String PLAYER_SCORE = "score";
	private static final String PLAYER_WINNER = "winner";
	
	private static final String CUSTOM_INFO_TABLE = "game_custom_info";	// V4
	private static final String CUSTOM_INFO_ID = "custom_id";	// V4
	private static final String CUSTOM_INFO_GAME_ID = "game_id";	// V4
	private static final String CUSTOM_INFO_MINPLAYERS = "custom_min";	// V4
	private static final String CUSTOM_INFO_MAXPLAYERS = "custom_max";	// V4
	
	public static final int OPERATION_SUCCESS = 0;
	public static final int OPERATION_SUCCESS_WITH_ERRORS = 1;
	public static final int OPERATION_FAIL = 2;
	
	private Context mContext;
	private SQLiteDatabase mDb;
	
	/**
	 * SQL Statements
	 */
	private static final String DATABASE_CREATE_GAMES = "CREATE TABLE " + GAME_TABLE + " (" + 
		GAME_ID + " INTEGER PRIMARY KEY, " +
		GAME_NAME + " TEXT NOT NULL, " +
		GAME_MINPLAYERS + " INTEGER, " +
		GAME_MAXPLAYERS + " INTEGER, " +
		GAME_BGGLENGTH + " INTEGER, " +
		GAME_BGGRATING + " REAL, " +
		GAME_MANUAL + " INTEGER DEFAULT 0" +
		")";
	
	private static final String DATABASE_CREATE_PLAY = "CREATE TABLE " + PLAY_TABLE + " (" +
		PLAY_ID + " INTEGER PRIMARY KEY, " +
		PLAY_GAME_ID + " INTEGER, " +
		PLAY_DATE + " TEXT, " +
		PLAY_LOCATION + " TEXT, " +
		PLAY_SCENARIO + " TEXT, " +
		PLAY_LENGTH + " INTEGER" +
		")";
	
	private static final String DATABASE_CREATE_PLAYER = "CREATE TABLE " + PLAYER_TABLE + " (" +
		PLAYER_ID + " INTEGER PRIMARY KEY, " +
		PLAYER_PLAY_ID + " INTEGER, " +
		PLAYER_NAME + " TEXT, " +
		PLAYER_USERNAME + " TEXT, " +
		PLAYER_TEAM + " TEXT, " +
		PLAYER_STARTINGPOS + " INTEGER, " +
		PLAYER_SCORE + " INTEGER, " +
		PLAYER_WINNER + " INTEGER DEFAULT 0" +
		")";
	
	private static final String DATABASE_CREATE_CUSTOM_INFO = "CREATE TABLE " + CUSTOM_INFO_TABLE + " (" +
		CUSTOM_INFO_ID + " INTEGER PRIMARY KEY, " +
		CUSTOM_INFO_GAME_ID + " INTEGER, " +
		CUSTOM_INFO_MINPLAYERS + " INTEGER, " +
		CUSTOM_INFO_MAXPLAYERS + " INTEGER" +
		")";
	
	
	private static final String TAG = "GameDbHelper";
	
	
	private static class DatabaseHelper extends SQLiteOpenHelper {
		Context mCtx;
		DatabaseHelper(Context context) {
			super(context, context.getResources().getString(R.string.db_file), null, DATABASE_VERSION);
			mCtx = context;
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.w(TAG, "Creating new database with version " + DATABASE_VERSION);
			db.execSQL(DATABASE_CREATE_GAMES);
			db.execSQL(DATABASE_CREATE_PLAY);
			db.execSQL(DATABASE_CREATE_PLAYER);
			db.execSQL(DATABASE_CREATE_CUSTOM_INFO);
		}
		
		@Override
		public void onOpen(SQLiteDatabase db) {
			Log.w(TAG, "Opening database...");
			super.onOpen(db);
			Toast.makeText(mCtx, "Database Version: " + db.getVersion(), Toast.LENGTH_SHORT).show();
		}
		
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion);
			
			if (newVersion <= oldVersion)
				return;
			
			db.beginTransaction();
			boolean success = true;
			for (int i = oldVersion; i < newVersion; i++) {
				int nextVersion = i+1;
				switch (nextVersion) {
				case 4:
					success = upgradeTo4(db);
					break;
				}
				
				if (!success) {
					break;
				}
			}
			if (success) {
				db.setTransactionSuccessful();
			}
			db.endTransaction();
			//db.execSQL("DROP TABLE IF EXISTS " + GAME_TABLE);
			//db.execSQL("DROP TABLE IF EXISTS " + PLAY_TABLE);
			//db.execSQL("DROP TABLE IF EXISTS " + PLAYER_TABLE);
			//onCreate(db);
		}
		
		private boolean upgradeTo4(SQLiteDatabase db) {
			try {
				db.execSQL("ALTER TABLE " + PLAY_TABLE + " ADD COLUMN " + PLAY_LOCATION + " TEXT");
				db.execSQL("ALTER TABLE " + PLAY_TABLE + " ADD COLUMN " + PLAY_SCENARIO + " TEXT");
				db.execSQL(DATABASE_CREATE_CUSTOM_INFO);
			} catch (SQLException se) {
				Log.w(TAG, "Error adding columns for Upgrade 4...\n" + se);
				return false;
			}
			
			return true;
		}
	}
	
	
	public UtilDbHelper(Context context) {
		mContext = context;
		DatabaseHelper helper = new DatabaseHelper(mContext);
		mDb = helper.getWritableDatabase();
	}
	
	public Integer getDbVersion() {
		return mDb.getVersion();
	}
	
	/**
	 * Get all the games in the DB
	 * @return A list of all the games
	 */
	public List<GameInfo> selectAllGames() {
		
		List<GameInfo> list = new ArrayList<GameInfo>();
		
		Cursor cursor = mDb.query(GAME_TABLE, 
				new String[] {GAME_ID, GAME_NAME, GAME_MINPLAYERS, GAME_MAXPLAYERS, GAME_BGGLENGTH, GAME_BGGRATING, GAME_MANUAL}, 
				null, null, null, null, "name");
		if (cursor.moveToFirst()) {
			do {
				list.add(readGameFromCursor(cursor));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}
	
	/**
	 * Select a list of games matching the number of players and game length
	 * @param numPlayers The number of players a game must support
	 * @param maxLength The maximum length the game must support; Only games
	 * which can be played in time less than this will be returned
	 * @return A list of matching games
	 */
	public List<GameInfo> selectMatchingGames(Integer numPlayers, Integer maxLength) {
		List<GameInfo> matchingGames = new ArrayList<GameInfo>();
		for (GameInfo game : selectAllGames()) {
			if (numPlayers >= game.getMinPlayers() &&
					numPlayers <= game.getMaxPlayers() &&
					maxLength >= game.getBggLength()) {
				matchingGames.add(game);
			}
		}
		
		return matchingGames;
	}
	
	/**
	 * Get the game information for a single game
	 * @param gameId The ID of the game to be selected
	 * @return An object containing the game information
	 */
	public GameInfo selectSingleGame(Integer gameId) {
		Cursor cursor = mDb.query(GAME_TABLE, 
				new String[] {GAME_ID, GAME_NAME, GAME_MINPLAYERS, GAME_MAXPLAYERS, GAME_BGGLENGTH, GAME_BGGRATING, GAME_MANUAL}, 
				GAME_ID+"=?", new String[] {gameId.toString()}, null, null, GAME_NAME);
		GameInfo game = new GameInfo();
		if (cursor.moveToFirst()) {
			game = readGameFromCursor(cursor);
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return game;
	}
	
	/**
	 * Record the customized information for a game
	 * @param custom The customized information to record
	 */
	public void recordCustomInfo(GameInfoCustom custom) {
		if (custom == null || custom.getParentGame() == null) return;
		if (custom.isEmpty()) {
			deleteCustomInfoForGame(custom.getParentGame().getGameId());
			return;
		}
		
		String gameId = custom.getParentGame().getGameId().toString();		
		Cursor cursor = mDb.query(CUSTOM_INFO_TABLE, new String[] {CUSTOM_INFO_ID},
				CUSTOM_INFO_GAME_ID+"=?", new String[] {gameId},
				null, null, null);
		boolean exists = false;
		if (cursor.moveToFirst()) exists = true;
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		
		ContentValues cv = new ContentValues();
		cv.put(CUSTOM_INFO_GAME_ID, custom.getParentGame().getGameId());
		cv.put(CUSTOM_INFO_MINPLAYERS, custom.getMinPlayers());
		cv.put(CUSTOM_INFO_MAXPLAYERS, custom.getMaxPlayers());
		
		if (exists) {
			mDb.update(CUSTOM_INFO_TABLE, cv, CUSTOM_INFO_GAME_ID+"=?", new String[] {gameId});
		} else {
			mDb.insert(CUSTOM_INFO_TABLE, null, cv);
		}
	}
	
	/**
	 * Delete the customized information for a game
	 * @param gameId The id of the game to remove the custom info from
	 */
	public void deleteCustomInfoForGame(Integer gameId) {
		mDb.delete(CUSTOM_INFO_TABLE, CUSTOM_INFO_GAME_ID+"=?", new String[] {gameId.toString()});
	}
	
	/**
	 * Get the customized information for a game
	 * @param game The game to get the custom info for
	 * @return The customized info for the game
	 */
	public GameInfoCustom getCustomInfoForGame(GameInfo game) {
		Cursor cursor = mDb.query(CUSTOM_INFO_TABLE,
				new String[] {CUSTOM_INFO_MINPLAYERS, CUSTOM_INFO_MAXPLAYERS},
				CUSTOM_INFO_GAME_ID+"=?", new String[] {game.getGameId().toString()}, null, null, null);
		GameInfoCustom custom = null;
		if (cursor.moveToFirst()) {
			custom = new GameInfoCustom(game);
			if (cursor.getInt(0) > 0)
				custom.setMinPlayers(cursor.getInt(0));
			if (cursor.getInt(1) > 0) 
				custom.setMaxPlayers(cursor.getInt(1));
		} 
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return custom;
	}
	
	/**
	 * Get the number of games listed in your collection.
	 * @return The number of games in the collection
	 */
	public Integer getGameCollectionSize() {
		Cursor cursor = mDb.rawQuery("SELECT COUNT(" + GAME_ID + ") AS numgames FROM " + GAME_TABLE, null);
		Integer size = 0;
		if (cursor.moveToFirst()) {
			size = cursor.getInt(0);
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return size;
	}
	
	/**
	 * Sychronize a list of games with those found in the DB, recording the amount
	 * of new games added/deleted from the DB.
	 * @param games
	 * @return
	 */
	public SyncInfo syncListWithDb(List<GameInfo> games) {
		SyncInfo sync = new SyncInfo();
		mDb.beginTransaction();
		try {
			List<Integer> gameIds = new ArrayList<Integer>();
			for (GameInfo game : games) {
				gameIds.add(game.getGameId());
				if (insertGame(game) != -1) {
					sync.incrementAdd();
				} else if (modifyGame(game)) {
					sync.incrementModified();
				}
			}
			deleteAllGamesExcept(gameIds, sync);
			mDb.setTransactionSuccessful();
		} finally {
			mDb.endTransaction();
		}
		return sync;
	}
	
	/** 
	 * Record a play of a game
	 * @param play The game play information to record
	 * @param playId If 0, creates a new play; otherwise, updates an existing play
	 * @return OPERATION_FAIL, OPERATION_SUCCESS, or OPERATION_SUCCESS_WITH_ERRORS, depending on what happens.
	 */
	public int recordPlay(GamePlayInfo play, long playId) {
		mDb.beginTransaction();
		ContentValues cv = new ContentValues();
		cv.put(PLAY_GAME_ID, play.getGame().getGameId());
		String gameDate = "";
		if (play.getGameDate() != null)	
			gameDate = DateFormat.getDateFormat(mContext).format(play.getGameDate());
		cv.put(PLAY_DATE, gameDate);
		cv.put(PLAY_LOCATION, play.getLocation());
		cv.put(PLAY_SCENARIO, play.getScenario());
		cv.put(PLAY_LENGTH, play.getGameLength());
		
		if (playId == 0) {
			try {
				playId = mDb.insertWithOnConflict(PLAY_TABLE, null, cv, SQLiteDatabase.CONFLICT_ABORT);
			} catch (Throwable t) {
				// TODO: somehow report the failure?
				mDb.endTransaction();
				return OPERATION_FAIL;
			}
			play.setPlayId(playId);
		} else {
			try {
				mDb.updateWithOnConflict(PLAY_TABLE, cv, PLAY_ID+"=?", new String[]{String.valueOf(playId)}, SQLiteDatabase.CONFLICT_ABORT);
				mDb.delete(PLAYER_TABLE, PLAYER_PLAY_ID+"=?", new String[]{String.valueOf(playId)});
			} catch (Throwable t) {
				// TODO: somehow report the failure?
				mDb.endTransaction();
				return OPERATION_FAIL;
			}
		}
		
		int returnCode = OPERATION_SUCCESS;
		for (GamePlayerInfo player : play.getPlayers()) {
			cv = new ContentValues();
			cv.put(PLAYER_PLAY_ID, playId);
			cv.put(PLAYER_NAME, player.getName());
			cv.put(PLAYER_USERNAME, player.getUsername());
			cv.put(PLAYER_TEAM, player.getTeam());
			cv.put(PLAYER_STARTINGPOS, player.getStartingPos());
			cv.put(PLAYER_SCORE, player.getScore());
			cv.put(PLAYER_WINNER, player.getWinner()?1:0);
			try {
				mDb.insertWithOnConflict(PLAYER_TABLE, null, cv, SQLiteDatabase.CONFLICT_ABORT);
			} catch (Throwable t) {
				// TODO: ignore it?
				returnCode = OPERATION_SUCCESS_WITH_ERRORS;
			}
		}
		mDb.setTransactionSuccessful();
		mDb.endTransaction();
		return returnCode;
	}
	
	/**
	 * Get the number of plays recorded for a specific game
	 * @param gameId The id of the game to check
	 * @return The number of plays recorded for the game
	 */
	public Integer getNumPlaysForGame(Long gameId) {
		Cursor cursor = mDb.rawQuery("SELECT COUNT(" + PLAY_ID + ") AS numplays FROM " + PLAY_TABLE + " WHERE " + PLAY_GAME_ID + "=?", new String[]{gameId.toString()});
		Integer numPlays = 0;
		if (cursor.moveToFirst()) {
			numPlays = cursor.getInt(0);
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return numPlays;
	}
	
	/**
	 * Get a single record of game play information
	 * @param playId The id of the game play record
	 * @return The game play information of the specified record
	 */
	public GamePlayInfo selectSinglePlay(Long playId) {
		GamePlayInfo play = null;
		
		Cursor cursor = mDb.query(PLAY_TABLE, 
				new String[] {PLAY_ID, PLAY_GAME_ID, PLAY_DATE, PLAY_LOCATION, PLAY_SCENARIO, PLAY_LENGTH},
				PLAY_ID + "=?", new String[] {playId.toString()}, null, null, PLAY_ID);
		if (cursor.moveToFirst()) {
			play = new GamePlayInfo();
			play.setPlayId(playId);
			play.setGame(selectSingleGame(cursor.getInt(1)));
			try {
				Date d = DateFormat.getDateFormat(mContext).parse(cursor.getString(2));
				play.setGameDate(d);
			} catch (ParseException pe) {
				//NOTHING
			}
			play.setLocation(cursor.getString(3));
			play.setScenario(cursor.getString(4));
			play.setGameLength(cursor.getInt(5));
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		
		play.getPlayers().addAll(getPlayersForPlay(playId));
		
		return play;
	}
	
	/**
	 * Get all plays for all games
	 * @param getPlayers If true, will retrieve the player information in the record; otherwise 
	 * only the overall play info will be retrieved.
	 * @return A list of game play records
	 */
	public List<GamePlayInfo> getAllPlays(boolean getPlayers) {
		List<GamePlayInfo> plays = new ArrayList<GamePlayInfo>();
		Map<Integer, GameInfo> games = new HashMap<Integer, GameInfo>();
		
		Cursor cursor = mDb.query(PLAY_TABLE,
				new String[] {PLAY_ID, PLAY_GAME_ID, PLAY_DATE, PLAY_LOCATION, PLAY_SCENARIO, PLAY_LENGTH},
				null, null, null, null, PLAY_ID);
		if (cursor.moveToFirst()) {
			do {
				GamePlayInfo play = new GamePlayInfo();
				play = getPlayInfoHelper(cursor);
				Integer gameId = cursor.getInt(1);
				GameInfo game;
				if (games.containsKey(gameId)) {
					game = games.get(gameId);
				} else {
					game = selectSingleGame(gameId);
					games.put(gameId, game);
				}
				
				play.setGame(game);
				
				plays.add(play);
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		
		for (GamePlayInfo play : plays) {
			if (getPlayers) {
				play.getPlayers().addAll(getPlayersForPlay(play.getPlayId()));
			} else {
				play.setNumPlayers(getNumPlayersForPlay(play.getPlayId()));
			}
		}
		
		Collections.sort(plays);
		Collections.reverse(plays);
		
		return plays;
	}
	
	/**
	 * Get all plays for a specific game without player information
	 * @param gameId The id of the game to retrieve play information for
	 * @return A list of play records for the specified game
	 */
	public List<GamePlayInfo> getPlaysForGame(Integer gameId) {
		return getPlaysForGame(gameId, false);
	}
	
	/**
	 * Gets all plays for a specific game
	 * @param gameId The id of the game to retrieve play information for
	 * @param getPlayers If true, will retrieve the player information for each record; otherwise, only
	 * the overall play info for each record will be retrieved.
	 * @return A list of play records for the specified game
	 */
	public List<GamePlayInfo> getPlaysForGame(Integer gameId, boolean getPlayers) {
		List<GamePlayInfo> plays = new ArrayList<GamePlayInfo>();
		
		if (gameId == 0) return getAllPlays(getPlayers);
		
		GameInfo game = selectSingleGame(gameId);
				
		Cursor cursor = mDb.query(PLAY_TABLE, 
				new String[] {PLAY_ID, PLAY_GAME_ID, PLAY_DATE, PLAY_LOCATION, PLAY_SCENARIO, PLAY_LENGTH},
				PLAY_GAME_ID + "=?", new String[] {game.getGameId().toString()}, null, null, PLAY_ID);
		if (cursor.moveToFirst()) {
			do {
				GamePlayInfo play = new GamePlayInfo();
				play = getPlayInfoHelper(cursor);
				play.setGame(game);
				
				plays.add(play);
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		
		for (GamePlayInfo play : plays) {
			if (getPlayers) {
				play.getPlayers().addAll(getPlayersForPlay(play.getPlayId()));
			} else {
				play.setNumPlayers(getNumPlayersForPlay(play.getPlayId()));
			}
		}
		
		Collections.sort(plays);
		Collections.reverse(plays);
		
		return plays;
	}
		
	/**
	 * Gets the player information for a game play record
	 * @param playId The id of the game play record
	 * @return A list of player information for the specified play record
	 */
	public List<GamePlayerInfo> getPlayersForPlay(Long playId) {
		List<GamePlayerInfo> players = new ArrayList<GamePlayerInfo>();
		if (playId != null) {
			Cursor cursor = mDb.query(PLAYER_TABLE, 
					new String[] {PLAYER_ID, PLAYER_PLAY_ID, PLAYER_NAME, PLAYER_USERNAME, PLAYER_TEAM, PLAYER_STARTINGPOS, PLAYER_SCORE, PLAYER_WINNER},
					PLAYER_PLAY_ID + "=?", new String[] {playId.toString()}, null, null, PLAYER_STARTINGPOS);
			
			if (cursor.moveToFirst()) {
				do {
					GamePlayerInfo player = new GamePlayerInfo();
					player.setName(cursor.getString(2));
					player.setUsername(cursor.getString(3));
					player.setTeam(cursor.getString(4));
					player.setStartingPos(cursor.getInt(5));
					player.setScore(cursor.getInt(6));
					player.setWinner(cursor.getInt(7)==1);
					
					players.add(player);		
				} while (cursor.moveToNext());
			}
			
			if (cursor != null & !cursor.isClosed()) {
				cursor.close();
			}
		}
		return players;
	}
	
	/**
	 * Gets the number of players recorded for a specific play record
	 * @param playId The id of the play record to get player count for
	 * @return The number of players recorded for the play
	 */
	public Integer getNumPlayersForPlay(Long playId) {
		Cursor cursor = mDb.rawQuery("SELECT COUNT(" + PLAYER_ID + ") AS numplayers FROM " + PLAYER_TABLE + " WHERE " + PLAYER_PLAY_ID + "=?", new String[]{playId.toString()});
		Integer numPlayers = 0;
		if (cursor.moveToFirst()) {
			numPlayers = cursor.getInt(0);
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return numPlayers;
	}
	
	/**
	 * Get a list of all player names recorded thus far
	 * @return A list of all players found in the database
	 */
	public List<String> getPlayerNames() {
		Cursor cursor = mDb.query(true, PLAYER_TABLE, new String[]{PLAYER_NAME}, null, null, null, null, PLAYER_NAME, null);
		List<String> players = buildListFromCursor(cursor);
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return players;
	}
	
	/**
	 * Renames a player
	 * @param oldName The name of the player to rename
	 * @param newName The new name of the player
	 * @return true if any replacements were made, false if not
	 */
	public boolean renamePlayer(String oldName, String newName) {
		ContentValues cv = new ContentValues();
		cv.put(PLAYER_NAME, newName);
		
		String whereString = PLAYER_NAME+"=?";
		
		String[] whereParams = { oldName };
		
		int numMod = mDb.update(PLAYER_TABLE, cv, whereString, whereParams);
		
		return numMod > 0;
	}
	
	
	/**
	 * Rename a team for a specific game; Teams for other games will not be affected, even if they
	 * have the same team name
	 * @param oldTeam The name of the team to change
	 * @param newTeam The new name of the team
	 * @param gameId The ID of the game to change the team for 
	 * @return True if at least one change was made, false otherwise
	 */
	public boolean renameTeamForGame(String oldTeam, String newTeam, Integer gameId) {
		String query = "UPDATE " + PLAYER_TABLE + " SET " + PLAYER_TEAM+"=?"  +
		" WHERE " + PLAYER_TEAM + "=? AND " + PLAYER_PLAY_ID + " IN" + 
		" (SELECT " + PLAY_ID + " FROM " + PLAY_TABLE + " WHERE " + PLAY_GAME_ID + "=?)"; 
	
		String[] args = {newTeam, oldTeam, gameId.toString()};
		mDb.execSQL(query, args);
		
		Cursor c = mDb.rawQuery("SELECT changes()", null);
		
		int rowsChanged = 0;
		
		if (c.moveToFirst()) {
			rowsChanged = c.getInt(0);
		}
		if (c != null && !c.isClosed()) {
			c.close();
		}
		
		return rowsChanged > 0;
	}
	
	
	/**
	 * Get a list of all player usernames recorded thus far
	 * @return A list of all usernames found in the database
	 */
	public List<String> getPlayerUsernames() {
		Cursor cursor = mDb.query(true, PLAYER_TABLE, new String[]{PLAYER_USERNAME}, null, null, null, null, PLAYER_USERNAME, null);
		List<String> usernames = buildListFromCursor(cursor);
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return usernames;
	}
	
	/**
	 * Get a list of all locations recorded thus far
	 * @return A list of all locations found in the database
	 */
	public List<String> getLocations() {
		Cursor cursor = mDb.query(true, PLAY_TABLE, new String[]{PLAY_LOCATION}, null, null, null, null, PLAY_LOCATION, null);
		List<String> locations = buildListFromCursor(cursor);
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return locations;
	}
	
	/**
	 * Get a list of all teams recorded for a specific game
	 * @param gameId The id of the game to retrieve the team list for
	 * @return A list of teams for the specified game
	 */
	public List<String> getTeamsForGame(Integer gameId) {
		String joinTable = PLAY_TABLE + " INNER JOIN " + PLAYER_TABLE + " ON " + PLAY_TABLE + "." + PLAY_ID + "=" + PLAYER_TABLE + "." + PLAYER_PLAY_ID;
		
		Cursor cursor = mDb.query(true, joinTable, new String[]{PLAYER_TABLE+"."+PLAYER_TEAM}, PLAY_TABLE+"."+PLAY_GAME_ID+"=?", new String[]{gameId.toString()}, null, null, PLAYER_TABLE+"."+PLAYER_TEAM, null);
		
		List<String> teams = buildListFromCursor(cursor);
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return teams;
	}
	
	/**
	 * Get a list of all scenarios recorded for a specific game
	 * @param gameId The id of the game to retrieve the scenario list for 
	 * @return A list of scenarios for the specified game
	 */
	public List<String> getScenariosForGame(Integer gameId) {
		String query = "SELECT DISTINCT " + PLAY_SCENARIO + " FROM " + GAME_TABLE +
		" INNER JOIN " + PLAY_TABLE + " ON " + GAME_TABLE + "." + GAME_ID + "=" + PLAY_TABLE + "." + PLAY_GAME_ID +
		" WHERE " + GAME_TABLE + "." + GAME_ID + "=?" +
		" ORDER BY " + PLAY_SCENARIO;
		Cursor cursor = mDb.rawQuery(query, new String[]{gameId.toString()});
		
		List<String> scenarios = buildListFromCursor(cursor);
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return scenarios;
	}
	
	
	
	
	/**
	 * Insert a game into the DB, if it's not already there
	 * @param game The game to insert
	 * @return The row ID of the game inserted, or SQLITE_CONSTRAINT if it was already there 
	 */
	private long insertGame(GameInfo game) {
		ContentValues cv = new ContentValues();
		cv.put(GAME_ID, game.getGameId());
		cv.put(GAME_NAME, game.getName());
		cv.put(GAME_MINPLAYERS, game.getMinPlayers());
		cv.put(GAME_MAXPLAYERS, game.getMaxPlayers());
		cv.put(GAME_BGGLENGTH, game.getBggLength());
		cv.put(GAME_BGGRATING, game.getBggRating());
		cv.put(GAME_MANUAL, game.isManualEntry()?1:0);
		
		try {
			long newid = mDb.insertWithOnConflict(GAME_TABLE, null, cv, SQLiteDatabase.CONFLICT_ABORT);
			return newid;
		} catch (Throwable t) {
			return -1;
		}
	}
	
	/**
	 * Modify a game in the DB, updating stats as necessary
	 * @param game The game to modify values to.
	 * @return True if the game exists and was modified, false otherwise
	 */
	private boolean modifyGame(GameInfo game) {
		
		ContentValues cv = new ContentValues();
		cv.put(GAME_MINPLAYERS, game.getMinPlayers());
		cv.put(GAME_MAXPLAYERS, game.getMaxPlayers());
		cv.put(GAME_BGGLENGTH, game.getBggLength());
		cv.put(GAME_BGGRATING, game.getBggRating());
		
		String whereString = GAME_ID+"=? AND (" +
							GAME_MINPLAYERS+"<>? OR " +
							GAME_MAXPLAYERS+"<>? OR " +
							GAME_BGGLENGTH+"<>? OR " +
							GAME_BGGRATING+"<>?)";
		
		String[] whereParams = { game.getGameId().toString(),
							game.getMinPlayers().toString(),
							game.getMaxPlayers().toString(),
							game.getBggLength().toString(),
							game.getBggRating().toString()
		};
		
		int numMod = mDb.update(GAME_TABLE, cv, whereString, whereParams);
		
		return numMod > 0;
	}
	
	/**
	 * Delete a single game from the DB
	 * @param game The game to delete
	 */
	private void deleteGame(Integer gameId) {
		mDb.delete(GAME_TABLE, GAME_ID+"=?", new String[] {gameId.toString()});
		mDb.delete(CUSTOM_INFO_TABLE, CUSTOM_INFO_GAME_ID+"=?", new String [] {gameId.toString()});
	}
		
	/**
	 * Remove all games from the collection except those in the list provided.
	 * @param gameIds A list of gameIds to keep in the database
	 * @param sync An object to record the number of games deleted
	 */
	private void deleteAllGamesExcept(List<Integer> gameIds, SyncInfo sync) {
		for (GameInfo game : selectAllGames()) {
			Integer gameId = game.getGameId();
			if (!gameIds.contains(gameId)) {
				deleteGame(gameId);
				sync.incrementDeleted();
			}
		}
	}
	
	/**
	 * Reads a game from a cursor object
	 * @param cursor The cursor object (IE row of the database)
	 * @return The game information contained in that row
	 */
	private GameInfo readGameFromCursor(Cursor cursor) {
		GameInfo game = new GameInfo();	
		game.setGameId(cursor.getInt(cursor.getColumnIndex(GAME_ID)));
		game.setName(cursor.getString(cursor.getColumnIndex(GAME_NAME)));
		game.setMinPlayers(cursor.getInt(cursor.getColumnIndex(GAME_MINPLAYERS)));
		game.setMaxPlayers(cursor.getInt(cursor.getColumnIndex(GAME_MAXPLAYERS)));
		game.setBggLength(cursor.getInt(cursor.getColumnIndex(GAME_BGGLENGTH)));
		game.setBggRating(cursor.getDouble(cursor.getColumnIndex(GAME_BGGRATING)));
		game.setManualEntry(cursor.getInt(cursor.getColumnIndex(GAME_MANUAL))==1);
		
		game.setCustomInfo(getCustomInfoForGame(game));
		
		return game;
	}
	
	private GamePlayInfo getPlayInfoHelper(Cursor cursor) {
		GamePlayInfo play = new GamePlayInfo();
		play.setPlayId((cursor.getLong(0)));
		try {
			Date d = DateFormat.getDateFormat(mContext).parse(cursor.getString(2));
			play.setGameDate(d);
		} catch (ParseException pe) {
			//NOTHING
		}
		play.setLocation(cursor.getString(3));
		play.setScenario(cursor.getString(4));
		play.setGameLength(cursor.getInt(5));
		
		return play;
	}	
	
	private List<String> buildListFromCursor(Cursor c) {
		List<String> list = new ArrayList<String>();
		if (c.moveToFirst()) {
			do {
				String item = c.getString(0);
				if (item != null)
					list.add(item);
			} while (c.moveToNext());
		}
		return list;
	}
	
}
